import json

import psycopg2
import mysql.connector
from psycopg2.extras import RealDictCursor


def get_postgis_connection():
    """
    获取postgis的数据库连接
    :return:
    """
    conn = psycopg2.connect(
        host="10.250.5.217",
        database="giscenter",
        user="postgres",
        password="eGova@pggis2023"
    )
    return conn


def get_mysql_connection():
    """
    获取MySQL数据库连接
    :return:
    """
    connection = mysql.connector.connect(
        host="10.255.40.58",
        database="xuanzang",
        user="root",
        password="eGovaZT@2023"
    )
    # connection = mysql.connector.connect(
    #     host="127.0.0.1",
    #     database="xz",
    #     user="root",
    #     password="egova"
    # )
    return connection


def postgresql_query(query):
    """
    执行postgis数据库的查询
    :param query:查询sql
    :return:
    """
    conn = get_postgis_connection()
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
        return result


def mysql_query(query):
    """
    执行MySQL数据的查询
    :param query: 查询sql
    :return:
    """
    conn = get_mysql_connection()
    with conn.cursor() as cursor:
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
        return result


def mysql_insert(query):
    """
    执行MySQL数据的插入
    :param query:
    :return:
    """
    conn = get_mysql_connection()
    with conn.cursor() as cursor:
        cursor.execute(query)
    conn.close()


def mysql_insert_many(querys, clear_table=True):
    """
    执行MySQL数据的插入
    :param query:
    :return:
    """
    conn = get_mysql_connection()
    try:

        with conn.cursor() as cursor:
            if clear_table:
                cursor.execute("truncate table gis_pipeline_stat_info;")
            for result_sql in querys:
                cursor.execute(result_sql)
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        conn.rollback()  # 出错时回滚
    finally:
        conn.close()



def gene_p_type(table_name, phy_layer_id):
    """
    管线类型统计
    :param table_name:
    :param phy_layer_id:
    :return:
    """

    sql = f"select p_type, count(*) as value from {table_name} group by p_type"
    data = postgresql_query(sql)
    insert_sql_model = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 11, '%s');"
    result_sqls = []
    for datum in data:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    return result_sqls


def gene_p_length(table_name, phy_layer_id, layer_type_id):
    """
    管线长度统计
    :param table_name:
    :param phy_layer_id:
    :return:
    """
    if is_point_layer(layer_type_id):
        return []
    sql1 = f"select sum(pipelength) as value from {table_name}"
    sql2 = f"select p_type, sum(pipelength) as value from {table_name}  GROUP BY p_type"

    data_1 = postgresql_query(sql1)
    data_2 = postgresql_query(sql2)
    insert_sql_model_1 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 21, '%s');"
    insert_sql_model_2 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 22, '%s');"
    result_sqls = []
    for datum in data_1:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model_1 % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    for datum in data_2:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model_2 % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    return result_sqls


def gene_subsid(table_name, phy_layer_id, layer_type_id):
    """
    管点的附属物统计
    :param table_name:
    :param phy_layer_id:
    :param layer_type_id:图层类型
    :return:
    """
    if not is_point_layer(layer_type_id):
        return []

    sql = f"select subsid, count(*) as value from {table_name}  group by subsid"

    data = postgresql_query(sql)
    insert_sql_model = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 31, '%s');"
    result_sqls = []
    for datum in data:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)
    return result_sqls


def gene_road(table_name, phy_layer_id, layer_type_id):
    """
    所属道路统计
    :param table_name:
    :param phy_layer_id:
    :param layer_type_id:
    :return:
    """
    sql1 = f"select roadname, count(*) as value from {table_name}  group by roadname"
    sql2 = f"select roadname, p_type, sum(pipelength) as value from {table_name} group by roadname, p_type;"
    sql3 = f"select roadname, p_type, subsid, count(*) as value from {table_name} group by roadname, p_type, subsid"

    data_1 = postgresql_query(sql1)

    insert_sql_model_1 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 41, '%s');"
    insert_sql_model_2 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 42, '%s');"
    insert_sql_model_3 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 43, '%s');"

    result_sqls = []
    for datum in data_1:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model_1 % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    if not is_point_layer(layer_type_id):
        data_2 = postgresql_query(sql2)
        for datum in data_2:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_2 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)

    if is_point_layer(layer_type_id):
        data_3 = postgresql_query(sql3)
        for datum in data_3:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_3 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)

    return result_sqls


def gene_qsdw(table_name, phy_layer_id, layer_type_id):
    """
    权属单位统计
    :param table_name:
    :param phy_layer_id:
    :param layer_type_id:
    :return:
    """
    sql1 = f"select qsdw, count(*) as value from {table_name} group by qsdw"
    sql2 = f"select qsdw, p_type, sum(pipelength) as value from {table_name} group by qsdw, p_type"
    sql3 = f"select qsdw, subsid, p_type, count(*) as value from {table_name} group by qsdw, p_type, subsid;"

    data_1 = postgresql_query(sql1)

    insert_sql_model_1 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 51, '%s');"
    insert_sql_model_2 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 52, '%s');"
    insert_sql_model_3 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 53, '%s');"

    result_sqls = []
    for datum in data_1:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model_1 % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    if not is_point_layer(layer_type_id):
        data_2 = postgresql_query(sql2)
        for datum in data_2:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_2 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)
    if is_point_layer(layer_type_id):
        data_3 = postgresql_query(sql3)
        for datum in data_3:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_3 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)

    return result_sqls


def gene_diameter(table_name, phy_layer_id, layer_type_id):
    """
    管径统计
    :param table_name:
    :param phy_layer_id:
    :return:
    """
    if is_point_layer(layer_type_id):
        return []
    sql = f"select diameter, p_type, sum(pipelength) as value from {table_name} group by diameter, p_type"
    data = postgresql_query(sql)
    insert_sql_model = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 61, '%s');"
    result_sqls = []
    for datum in data:
        json_data = json.dumps(datum, ensure_ascii=False)

        insert_sql = insert_sql_model % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    return result_sqls


def gene_material(table_name, phy_layer_id, layer_type_id):
    """
    材质统计
    :param table_name:
    :param phy_layer_id:
    :return:
    """
    if is_point_layer(layer_type_id):
        return []
    sql = f"select material, p_type, sum(pipelength) as value from {table_name} group by material, p_type"
    data = postgresql_query(sql)
    insert_sql_model = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 71, '%s');"
    result_sqls = []
    for datum in data:
        json_data = json.dumps(datum, ensure_ascii=False)

        insert_sql = insert_sql_model % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    return result_sqls


def gene_ssqy(table_name, phy_layer_id, layer_type_id):
    """
    所属区域统计
    :param table_name:
    :param phy_layer_id:
    :param layer_type_id:
    :return:
    """
    sql1 = f"select ssqy, count(*) as value from {table_name} group by ssqy"
    sql2 = f"select ssqy, p_type, sum(pipelength) as value from {table_name} group by ssqy, p_type"
    sql3 = f"select ssqy, subsid, p_type, count(*) as value from {table_name} group by ssqy, p_type, subsid"

    data_1 = postgresql_query(sql1)

    insert_sql_model_1 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 81, '%s');"
    insert_sql_model_2 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 82, '%s');"
    insert_sql_model_3 = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 83, '%s');"

    result_sqls = []
    for datum in data_1:
        json_data = json.dumps(datum, ensure_ascii=False)
        insert_sql = insert_sql_model_1 % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    if not is_point_layer(layer_type_id):
        data_2 = postgresql_query(sql2)
        for datum in data_2:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_2 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)

    if is_point_layer(layer_type_id):
        data_3 = postgresql_query(sql3)
        for datum in data_3:
            json_data = json.dumps(datum, ensure_ascii=False)
            insert_sql = insert_sql_model_3 % (phy_layer_id, json_data)
            print(insert_sql)
            result_sqls.append(insert_sql)
    return result_sqls


def gene_build_time(table_name, phy_layer_id, layer_type_id):
    """
    年份统计
    :param table_name:
    :param phy_layer_id:
    :return:
    """
    if is_point_layer(layer_type_id):
        return []
    sql = f"select build_time, p_type, sum(pipelength) as value from {table_name} group by build_time, p_type"
    data = postgresql_query(sql)
    insert_sql_model = "insert into gis_pipeline_stat_info(layer_id, stat_type, data) values (%d, 91, '%s');"
    result_sqls = []
    for datum in data:
        datum['build_time'] = datum['build_time'].isoformat()
        json_data = json.dumps(datum, ensure_ascii=False)

        insert_sql = insert_sql_model % (phy_layer_id, json_data)
        print(insert_sql)
        result_sqls.append(insert_sql)

    return result_sqls


def is_point_layer(layer_type_id):
    """
    判断指定的图层类型是否为点图层
    :param layer_type_id:
    :return:
    """
    if layer_type_id % 10 == 1:
        return True
    return False


def stat(phy_layer_ids):
    query_vector_layer = f"select phy_layer_id, tb_name, layer_type_id from gis_vector_layer where phy_layer_id in({phy_layer_ids})"
    layers_info = mysql_query(query_vector_layer)
    result_sqls = []
    for phy_layer_id, phy_layer_tname, layer_type_id in layers_info:
        result_sqls.extend(gene_p_type(phy_layer_tname, phy_layer_id))
        result_sqls.extend(gene_p_length(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_subsid(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_road(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_qsdw(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_diameter(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_material(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_ssqy(phy_layer_tname, phy_layer_id, layer_type_id))
        result_sqls.extend(gene_build_time(phy_layer_tname, phy_layer_id, layer_type_id))
    return result_sqls


if __name__ == '__main__':
    # result_sqls = stat("10072,10073,10074,10075,10076,10077")
    result_sqls = stat("10128,10129,10130,10131,10132,10133")
    mysql_insert_many(result_sqls)
